package com.css.bjdt_core.ryxx.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.css.bjdt_core.ryxx.entity.Dict;
import com.css.bjdt_core.ryxx.entity.DnDj;
import com.css.bjdt_core.ryxx.entity.DnDy;
import com.css.bjdt_core.ryxx.entity.MRyInfo;
import com.css.bjdt_core.ryxx.entity.ModifyRyInfo;
import com.css.bjdt_core.util.CommonUtil;

@Service
public class MRyInfoService {
	@Autowired
	private Dao dao;

	/**
	 * 分页查询该党组织ID下的所有 党员信息
	 * @return
	 */
	public Map queryRyxxsPage(int pageNum, int pageSize, String idLst) {
		Map<String, Object> result = new HashMap<String, Object>();
		String[] arrids = {};
		if (StringUtils.isNoneBlank(idLst)) {
			arrids = idLst.split(",");
		} else {
			return result;
		}
		pageNum = (pageNum < 1) ? 1 : pageNum;
		pageSize = (pageSize < 1) ? 10 : pageSize;
		int start = (pageNum - 1) * pageSize;
		String[] tempids = Arrays.copyOfRange(arrids, start, start + pageSize);

		String sqlStr = "	SELECT	t1.ryid,t4.dzzjc,t1.xm,txb.dmcpt AS xbc,t1.xb,mz.dmcpt AS mzc,t1.mz, "
				/*+ " TO_CHAR (SYSDATE, 'yyyy') -TO_NUMBER ( SUBSTR(t1.zjhm, 7, 4)) as age,to_char(t3.rdrq,'yyyy-mm-dd') as rdrqc, "*/
				+ " t1.zjhm,to_char(t3.rdrq,'yyyy-mm-dd') as rdrqc, "
				+ " xl.dmcpt as xlc, t2.zgxl, zc.dmcpt as zcc, t2.zc, t2.dylb,t2.szzb FROM	t_m_ryinfo t1 "
				+ " left JOIN t_dn_dy t2 ON t1.RYID = t2.ryid" + " left JOIN T_M_ZZINFO t4 ON t2.szzb = t4.dzzid"
				+ " left JOIN T_DM_GB22611_XB txb ON txb.dmcod = t1.xb "
				+ " left JOIN T_DM_GB3304_MZ mz ON mz.dmcod = t1.mz"
				+ " left join T_DM_ZB64_RDSXL xl on xl.dmcod = t2.zgxl"
				+ " left join T_DM_GB8561_RDSZC zc on t2.zc = zc.dmcod" + " LEFT JOIN t_dn_dj t3 ON t2.ryid = t3.DY_ID "
				+ " WHERE " + " t1.ryid in (@ryids)";
		//+ " AND T2.szzb = @dzzId";
		Sql sql = Sqls.create(sqlStr);

		//sql.params().set("dzzId", dzzId);
		sql.params().set("ryids", tempids);
		//Pager pager = dao.createPager(pageNum, pageSize);
		//sql.setPager(pager);
		sql.setCallback(new SqlCallback() {

			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<MRyInfo> mRyInfoList = new ArrayList<MRyInfo>();
				while (rs.next()) {
					MRyInfo mRyInfo = new MRyInfo();
					mRyInfo.setRyid(rs.getString("RYID"));
					mRyInfo.setDzzjc(rs.getString("DZZJC"));
					mRyInfo.setXm(rs.getString("XM"));
					mRyInfo.setXbc(rs.getString("XBC"));
					mRyInfo.setMzc(rs.getString("MZC"));
					mRyInfo.setRdrqc(rs.getString("RDRQC"));
					mRyInfo.setXlc(rs.getString("XLC"));
					mRyInfo.setZcc(rs.getString("ZCC"));
					mRyInfo.setDylb(rs.getString("DYLB"));
					mRyInfo.setZjhm(rs.getString("ZJHM"));
					int age = CommonUtil.getAgeByIdCard(rs.getString("ZJHM"));
					if (age != 0) {
						mRyInfo.setAge(age);
					}
					mRyInfoList.add(mRyInfo);
				}
				return mRyInfoList;
			}
		});

		dao.execute(sql);
		List<MRyInfo> lst = sql.getList(MRyInfo.class);

		result.put("Rows", lst);
		/*Map<String, Object> params = new HashMap();
		params.put("ryids", arrids);*/
		int count = arrids.length; /*PageQuerys.countDistinctSql(dao, sqlStr, null, params);*/

		result.put("Total", count);
		return result;
	}

	public MRyInfo getRyInfo(String ryId) {
		String sqlStr = "	SELECT  distinct 	t1.ryid,t4.dzzjc,t1.xm,t1.xb,t1.mz,t3.rdrq,t1.csrq,"
				+ "  t2.zgxl, t2.zc, t2.dylb,t1.zjhm,t3.rdrq," + " t2.szzb,t3.RDSXSHJC,t2.jg,"
				+ " t2.NMGBZ,t2.GZGWMC,t2.LXFS,t2.zgxl,t2.xshjcqk,"
				+ " t2.zzrq,t2.djzt,t2.jtzz,t2.sfsldy,t2.slrq,t2.sflddy,t2.wclx   FROM	t_m_ryinfo t1 "
				+ " INNER JOIN t_dn_dy t2 ON t1.RYID = t2.ryid" + " INNER JOIN T_M_ZZINFO t4 ON t2.szzb = t4.dzzid"
				/*+ " LEFT JOIN t_dn_dj t3 ON t2.ryid = t3.DY_ID " + " WHERE t1.deleteflag = 0" + " AND t2.qybz = 'Y'"*/
				+ " LEFT JOIN t_dn_dj t3 ON t2.ryid = t3.DY_ID " + " WHERE " + " t1.ryid =@ryId";
		Sql sql = Sqls.create(sqlStr);
		sql.params().set("ryId", ryId);
		sql.setCallback(new SqlCallback() {

			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<MRyInfo> mRyInfoList = new ArrayList<MRyInfo>();
				while (rs.next()) {
					MRyInfo mRyInfo = new MRyInfo();
					mRyInfo.setRyid(rs.getString("RYID"));
					mRyInfo.setDzzjc(rs.getString("DZZJC"));
					mRyInfo.setXm(rs.getString("XM"));
					mRyInfo.setXb(rs.getString("XB"));
					mRyInfo.setMz(rs.getString("MZ"));
					mRyInfo.setCsrq(rs.getDate("CSRQ"));
					mRyInfo.setDylb(rs.getString("DYLB"));
					mRyInfo.setRdrq(rs.getDate("RDRQ"));//入党日期
					mRyInfo.setJg(rs.getString("JG"));
					mRyInfo.setZjhm(rs.getString("ZJHM"));
					mRyInfo.setGzgwmc(rs.getString("GZGWMC"));
					mRyInfo.setNmgbz(rs.getString("NMGBZ"));
					mRyInfo.setZgxl(rs.getString("ZGXL"));
					mRyInfo.setXshjcqk(rs.getString("XSHJCQK"));
					mRyInfo.setZc(rs.getString("ZC"));
					mRyInfo.setLxfs(rs.getString("LXFS"));
					mRyInfo.setZzrq(rs.getDate("ZZRQ"));
					mRyInfo.setDjzt(rs.getString("DJZT"));
					mRyInfo.setJtzz(rs.getString("JTZZ"));
					mRyInfo.setSfsldy(rs.getString("SFSLDY"));
					mRyInfo.setSlrq(rs.getDate("SLRQ"));
					mRyInfo.setSflddy(rs.getString("SFLDDY"));

					mRyInfoList.add(mRyInfo);
				}

				return mRyInfoList;
			}
		});

		dao.execute(sql);
		List<MRyInfo> lst = sql.getList(MRyInfo.class);
		if (lst.size() == 0) {
			return null;
		} else {
			MRyInfo obj = lst.get(0);
			return obj;
		}
	}

	/**
	 * 根据数字字典的表名，获得对应的list信息.(数据字典表：性别、名族...)
	 * @param tableName
	 * @return
	 */
	public List getDisList(String tableName) {
		Sql sql = Sqls.create("select dmcod,dmcpt from $table");
		sql.vars().set("table", tableName);
		sql.setCallback(new SqlCallback() {

			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<Dict> list = new ArrayList<Dict>();
				while (rs.next()) {
					Dict obj = new Dict();
					obj.setDmcod(rs.getString("DMCOD"));
					obj.setDmcpt(rs.getString("DMCPT"));
					list.add(obj);
				}
				return list;
			}
		});
		dao.execute(sql);
		return sql.getList(Dict.class);
	}

	public MRyInfo getMRyInfo(String id) {
		MRyInfo mRyInfo = dao.fetch(MRyInfo.class, Cnd.where("ryid", "=", id));

		return mRyInfo;
	}

	public DnDy getDnDyInfo(String id) {
		DnDy obj = dao.fetch(DnDy.class, Cnd.where("ryid", "=", id));

		return obj;
	}

	public DnDj getDnDjInfo(String id) {
		DnDj obj = dao.fetch(DnDj.class, Cnd.where("dy_id", "=", id));

		return obj;
	}

	public int deleteMRyInfo(String ryid) {
		return dao.delete(MRyInfo.class, ryid);

	}

	public MRyInfo insert(MRyInfo obj) {
		return dao.insert(obj);
	}

	public int update(MRyInfo obj) {
		return dao.update(obj);
	}

	public int updateIgnoreNull(MRyInfo obj) {
		return dao.updateIgnoreNull(obj);
	}

	public int updateIgnoreNull(DnDy obj) {
		return dao.updateIgnoreNull(obj);
	}

	public int updateIgnoreNull(DnDj obj) {
		return dao.update(obj);
	}

	public ModifyRyInfo insertModifyRyInfo(ModifyRyInfo obj) {
		return dao.insert(obj);
	}

	public ModifyRyInfo getModifyRyInfo(String ryid) {
		ModifyRyInfo modifyRyInfo = dao.fetch(ModifyRyInfo.class, Cnd.where("ryid", "=", ryid));

		return modifyRyInfo;
	}

	public int deleteModifyRyInfo(ModifyRyInfo obj) {
		return dao.delete(obj);
	}

}
